package com.teng.dao;

import java.sql.Connection;
import java.sql.Timestamp;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import java.util.Map;

import com.teng.entity.UserEntity;
import com.teng.util.DBUtil;

public class UserDao {
	
	public UserEntity myaccount(String id)throws Exception{
		Connection connection=DBUtil.getConnection();
		try {
			String sql=" select * from sys_user where id=?";
			PreparedStatement ps=connection.prepareStatement(sql);
			ps.setString(1, id);
			ResultSet rs=ps.executeQuery();
			UserEntity ue = null;
			if(rs.next()) {
				ue=row2entity(rs);
			}
			return ue;
		}finally {
		connection.close();	
		}
	
	}
	
	public int totalaccount()throws Exception{
		Connection connection=DBUtil.getConnection();
		try {
			String sql=" select count(*) from sys_user  where deleted=0";
			PreparedStatement ps=connection.prepareStatement(sql);
			ResultSet rs=ps.executeQuery();
			int a=0;
			if(rs.next()) {
			a=rs.getInt(1);
			}
			return a;
		}finally {
		connection.close();	
		}
	}
	
	public void insert(UserEntity u) throws Exception {
		Connection connection=DBUtil.getConnection();
		try {
			StringBuilder sql=new StringBuilder();
			sql.append("insert into ")
				.append(" sys_user")
				.append("(id,account,name,password,create_time,last_Login_IP,last_login_time,last_modify_password_time,phone,security_Email,avatar,remark,flag,deleted,create_user_id)")
				.append("values")
				.append("(?,?,?,?,?,  ?,?,?,?,?, ?,?,?,?,?)");
			PreparedStatement ps=connection.prepareStatement(sql.toString());
			int i=1;
			
			ps.setString(i++, u.getId());
			ps.setString(i++, u.getAccount());
			ps.setString(i++, u.getName());
			ps.setString(i++, u.getPassword());
			ps.setTimestamp(i++,toTimestamp(u.getCreateTime()));
			
			ps.setString(i++, u.getLastLoginIP());
			ps.setTimestamp(i++,  toTimestamp(u.getLastLoginTime()));
			ps.setTimestamp(i++, toTimestamp(u.getLastModifyPasswordTime()));
			ps.setString(i++, u.getPhone());
			ps.setString(i++, u.getSecurityEmail());
			
			ps.setString(i++, u.getAvatar());
			ps.setString(i++, u.getRemark());
			ps.setInt(i++, u.getFlag());
			ps.setInt(i++, u.getDeleted());
			ps.setString(i++, u.getCreateUserId());
			ps.executeUpdate();
			connection.commit();
		}finally {
			connection.close();
		}
	}
	
	
	public void unlock(String id)throws Exception{
		Connection connection=DBUtil.getConnection();
		try {
			String sql=" update sys_user set flag=0 where id=?";
			PreparedStatement ps=connection.prepareStatement(sql);
			ps.setString(1, id);
			ps.executeUpdate();
			connection.commit();
		}finally {
		connection.close();	
		}
	}
	
	
	public void lock(String id)throws Exception{
		Connection connection=DBUtil.getConnection();
		try {
			String sql=" update sys_user set flag=1 where id=?";
			PreparedStatement ps=connection.prepareStatement(sql);
			ps.setString(1, id);
			ps.executeUpdate();
			connection.commit();
		}finally {
		connection.close();	
		}
	}
	
	
	public void delete(String id) throws Exception {
			Connection connection=DBUtil.getConnection();
			try {
				String sql="update sys_user set deleted=1  where id=?";
				PreparedStatement ps=connection.prepareStatement(sql);
				ps.setString(1, id);
				ps.executeUpdate();
				connection.commit();
			}finally {
				connection.close();
			}
	}
	public int selectByLike(List<UserEntity> rows, String key, Integer state, int pageNo, int pageSize,String creatId)throws Exception{
		Connection connection=DBUtil.getConnection();
		try {
			StringBuilder sql=new StringBuilder();
				sql.append("select count(*) from sys_user where deleted=0 and create_user_id=? ");
				if(key!=null) {
				sql.append(" and (account like ? or name like ? or phone like ? or remark  like ?) ");
				}
				if(state!=null) {
					sql.append(" and flag=?");
				}
			PreparedStatement ps=connection.prepareStatement(sql.toString());
			int i=1;
			ps.setString(i++,creatId);
			if(key!=null) {
				key="%"+key+"%";
				ps.setString(i++, key);
				ps.setString(i++, key);
				ps.setString(i++, key);
				ps.setString(i++, key);
			}
			if(state!=null) {
				ps.setInt(1, state);
			}
			ResultSet rs=ps.executeQuery();
			int totalRows=0;
			if(rs.next()) {
				totalRows=rs.getInt(1);
			}
			if(totalRows==0) {
				return totalRows;
			}
			
			
			
			StringBuilder selectSql = new StringBuilder();
			selectSql.append(" select * from sys_user where deleted=0 and create_user_id=?");
			if(key!=null) {
				selectSql.append(" and (account like ? or name like ? or phone like ? or remark  like ?) ");
				}
				if(state!=null) {
					selectSql.append(" and flag=? ");
				}
			selectSql.append(" order by account ");
			selectSql.append(" limit ")
			.append(pageSize*(pageNo-1)).append(',').append(pageSize);
			ps=connection.prepareStatement(selectSql.toString());
			
			i=1;
			ps.setString(i++,creatId);
			if(key!=null) {
				key="%"+key+"%";
				ps.setString(i++, key);
				ps.setString(i++, key);
				ps.setString(i++, key);
				ps.setString(i++, key);
			}
			if(state!=null) {
				ps.setInt(1, state);
			}
			
			rs=ps.executeQuery();
			while(rs.next()){
				rows.add(row2entity(rs));
			}
			return totalRows;
		}finally {
			connection.close();
		}
	}
	
	
	
	
	
	public void update(String id, Map<String, Object> map) throws Exception {
			Connection connection=DBUtil.getConnection();
			try {
				StringBuilder sql=new StringBuilder();
				sql.append(" update sys_user set account=?,name=?,remark=?")
					.append(" where id=?");
				PreparedStatement ps=connection.prepareStatement(sql.toString());
				int i=1;
				ps.setString(i++, (String)map.get("account"));
				ps.setString(i++, (String)map.get("name"));
				ps.setString(i++, (String)map.get("remark"));
				ps.setString(i++, id);
				ps.executeUpdate();
				connection.commit();
			}finally {
				connection.close();
			}
	}
	
	public boolean validateAccount(String account) throws Exception{
			Connection connection=DBUtil.getConnection();
			try {
				String sql="select count(*) from sys_user where account=?";
				PreparedStatement ps=connection.prepareStatement(sql);
				ps.setString(1, account);
				ResultSet rs=ps.executeQuery();
				if(rs.next()) {
					return rs.getInt(1)>0;
					
				}
			}finally {
				connection.close();
			}
			return false;
	}
	
	
	
	public UserEntity selectById(String id) throws Exception {
		Connection connection=DBUtil.getConnection();
		UserEntity u=null;
		try {
			StringBuilder sql=new StringBuilder();
			sql.append(" select * from sys_user ")
			.append(" where id=?");
			PreparedStatement ps=connection.prepareStatement(sql.toString());
			ps.setString(1, id);
			ResultSet rs=ps.executeQuery();
			
			if(rs.next()) {
				u=row2entity(rs);
			}
		}finally {
			connection.close();
		}
		return u;
	}

	public UserEntity selectByAccount(String account) throws Exception {
		Connection connection=DBUtil.getConnection();
		try {
		StringBuilder sql=new StringBuilder();
		sql.append("select * from sys_user where account=?");
		PreparedStatement ps=connection.prepareStatement(sql.toString());
		ps.setString(1, account);
		ResultSet rs=ps.executeQuery();
		if(rs.next()) {
			return row2entity(rs);
		}
		
		return null;
		}finally {
			connection.close();
		}
	}

	private UserEntity row2entity(ResultSet rs) throws SQLException {
		UserEntity e=new UserEntity();
		e.setName(rs.getString("name"));
		e.setLastModifyPasswordTime(rs.getTimestamp("last_modify_password_time"));
		e.setFlag(rs.getInt("flag"));
		e.setId(rs.getString("id"));
		e.setAccount(rs.getString("account"));
		e.setPassword(rs.getString("password"));
		e.setCreateTime(rs.getTimestamp("create_time"));
		e.setLastLoginIP(rs.getString("last_login_ip"));
		e.setLastLoginTime(rs.getTimestamp("last_login_time"));
		e.setPhone(rs.getString("phone"));
		e.setSecurityEmail(rs.getString("security_email"));
		e.setAvatar(rs.getString("avatar"));
		e.setRemark(rs.getString("remark"));
		String createUserId=rs.getString("create_User_id");
		if(createUserId!=null){
			UserEntity createUser= new UserEntity();
			createUser.setId(createUserId);
			e.setCreateUser(createUser);
		}
		
		return e;
	}
	public static Timestamp toTimestamp(java.util.Date d ) {
		if (d != null) {
			return  new Timestamp(d.getTime());
		} 
		return null;
	}

}
